The City Of Melbourne Council is looking for ideas to bring people back into the city post covid, especially as a lot of businesses left the city permanently and the foot traffic from city workers has also seriously decreased.
An option to get people back into Melbourne City is to utilise the coworking businesses that are all throughout the city, and encourage workers in the city to support local businesses (in this analysis food and drink businesses) that are nearby to those office spaces.
As people have been working from home, and no longer might have an office to go to and collaborate, this use case provides a way for colleagues and friends to find a coworking office space that is central to everyone, and explore the café, restaurant, bistro businesses that may be nearby. This type of exploration into the city of Melbourne can encourage occasional city workers to move around the city, trying different coworking office spaces and supporting the local businesses around them.
At the end of this use case you will:
The below libraries are required to run this notebook. If you do not have any of then installed, or you get an error, be sure to pip install them like below:
!pip install folium
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import requests
import math
import random
import ipywidgets as widgets
import json
import folium
from math import radians, cos, sin, asin, sqrt
from folium.plugins import MarkerCluster
Below are the dataset that have been chosen form the City of Melbourne Open Data website:
seats_dataset= 'cafes-and-restaurants-with-seating-capacity'
coworking_dataset = 'coworking-spaces'
trains = 'metro-train-stations-with-accessibility-information'
First we will start by setting up a bse URL for the 3 datasets to use as they have some general informatioon that is shared.
BASE_URL = 'https://data.melbourne.vic.gov.au/api/v2/catalog/datasets/'
BASE_URL_v1 = 'https://data.melbourne.vic.gov.au/api/records/1.0/search/?dataset='
NUMBER_OF_RECORDS = 300
The coworking spaces listed are currently only located within the City of Melbourne municipal boundary. The list is not exhaustive. There may be other coworking spaces within the City of Melbourne municipality.
# check the number records for Co-Working paces
num_records_url = f'{BASE_URL_v1}{coworking_dataset}'
r = requests.get(num_records_url)
data_size = r.json()['nhits']
print(f'({coworking_dataset}) Total Records: {data_size}')
(coworking-spaces) Total Records: 66
As the dataset will fit in one API call (<300), let's get the data as a JSON dump
# Build up the whole URL using f strings
url_filter = f'exports/json?limit={NUMBER_OF_RECORDS}&offset=0&timezone=UTC'
url = f'{BASE_URL}{coworking_dataset}/{url_filter}'
# Call the API to get the dataset
coworking_result = requests.get(url)
coworking_result_json = coworking_result.json()
coworking = pd.DataFrame(coworking_result_json)
# View the dataset
coworking.head()
| organisation | address | website | latitude | longitude | geopoint | |
|---|---|---|---|---|---|---|
| 0 | 11th Space | Level 11/580 Collins St, Melbourne VIC 3000 | https://11thspace.com/ | -37.818523 | 144.955364 | {'lon': 144.9553637, 'lat': -37.818523} |
| 1 | 360 Collins | 360 Collins Street, Melbourne 3000 | https://officespace.com.au/melbourne/153369/ | -37.816002 | 144.962311 | {'lon': 144.962311, 'lat': -37.816002} |
| 2 | ACMI X | Level 4, 2 Kavanagh Street, Southbank 3006 | https://www.acmi.net.au/acmi-x/ | -37.821801 | 144.967594 | {'lon': 144.9675938, 'lat': -37.8218014} |
| 3 | Clik Collective | 2 Chelmsford Street, Kensington 3031 | http://clikcollective.com.au | -37.796415 | 144.931696 | {'lon': 144.9316956, 'lat': -37.7964148} |
| 4 | Hatch Quarter | 7/677 La Trobe Street, Docklands 3008 | http://www.hatchquarter.com.au/ | -37.814600 | 144.947310 | {'lon': 144.94731, 'lat': -37.8146} |
Data collected as part of the City of Melbourne's Census of Land Use and Employment (CLUE). The data covers the period 2002-2021. It show business establishments with their trading name, business address, industry (ANZSIC4) classification, number of dining seats (classified by indoor/outdoor), location and CLUE block and small area designation.
#Check the size of the Seats dataset
seats_filter = '&refine.census_year=2021'
num_records_url = f'{BASE_URL_v1}{seats_dataset}&q=&rows=1&start=0{seats_filter}'
r = requests.get(num_records_url)
data_size = r.json()['nhits']
print(f'({seats_dataset}) Total Records: {data_size}')
(cafes-and-restaurants-with-seating-capacity) Total Records: 3157
Increase the rows requested to 4,000 to capture the whole dataset for 2021
# Build the URL
num_records_url = f'{BASE_URL_v1}{seats_dataset}&q=&rows=4000&start=0{seats_filter}'
# Call the API and get the dataset
r = requests.get(num_records_url)
seats_json = r.json()
# Limit the retuend JSON data to just the 'records'
records = seats_json['records']
# Flatten the JSON data to a table
records_df = pd.json_normalize(records)
#Get the new columns names for the JSON data
columns = records_df.columns.to_list()
columns
['datasetid', 'recordid', 'record_timestamp', 'fields.location', 'fields.seating_type', 'fields.census_year', 'fields.property_id', 'fields.base_property_id', 'fields.trading_name', 'fields.block_id', 'fields.industry_anzsic4_description', 'fields.number_of_seats', 'fields.building_address', 'fields.clue_small_area', 'fields.business_address', 'fields.industry_anzsic4_code', 'fields.longitude', 'fields.latitude', 'geometry.type', 'geometry.coordinates']
Limit the dataset to only the oclumns which have "field" in the name indicating that they are the field values and not the meta data.
seats = pd.DataFrame(records_df[[c for c in columns if c.startswith('fields') ==True]])
column_names = [c.replace('fields.','') for c in seats.columns.to_list()]
seats.columns= column_names
# Remove unwanted columns form the dataset
seats.drop(columns=['location', 'census_year', 'property_id', 'base_property_id', 'block_id', 'clue_small_area', 'industry_anzsic4_code'], inplace=True)
seats.rename(columns = {'industry_anzsic4_description':'business_type'}, inplace=True)
seats.head(2)
| seating_type | trading_name | business_type | number_of_seats | building_address | business_address | longitude | latitude | |
|---|---|---|---|---|---|---|---|---|
| 0 | Seats - Indoor | Tokyo Maki | Cafes and Restaurants | 44 | 545-557 Flinders Street MELBOURNE VIC 3000 | 547 Flinders Street MELBOURNE VIC 3000 | 144.95651 | -37.82098 |
| 1 | Seats - Indoor | Domino's Pizza | Takeaway Food Services | 12 | 545-557 Flinders Street MELBOURNE VIC 3000 | 553 Flinders Street MELBOURNE VIC 3000 | 144.95651 | -37.82098 |
# remove duplicate rows for row with max seats for business
idx = seats.groupby(["trading_name", "business_address"])["number_of_seats"].idxmax()
# Subset the DataFrame using the index found above
seats = seats.loc[idx].reset_index(drop=True)
Have a look at the types of buinesses in the dataset
seats['business_type'].unique()
array(['Cafes and Restaurants', 'Takeaway Food Services', 'Clubs (Hospitality)', 'Non-Residential Property Operators', 'Catering Services', 'Accommodation', 'Pubs, Taverns and Bars', 'Car Retailing', 'Sports and Physical Recreation Venues, Grounds and Facilities Operation', 'Other Specialised Food Retailing', 'Bakery Product Manufacturing (Non-factory based)', 'Fruit and Vegetable Retailing', 'Flower Retailing', 'Motion Picture Exhibition', 'Performing Arts Venue Operation', 'Casino Operation', 'Newspaper and Book Retailing', 'Supermarket and Grocery Stores', 'Amusement and Other Recreational Activities n.e.c.', 'Horse and Dog Racing Administration and Track Operation', 'Other Gambling Activities', 'Automotive Body, Paint and Interior Repair', 'Zoological and Botanical Gardens Operation', 'Other Administrative Services n.e.c.', 'Other Store-Based Retailing n.e.c.', 'Performing Arts Operation', 'Telecommunication Goods Wholesaling', 'Other Food Product Manufacturing n.e.c.', 'Museum Operation', 'Other Interest Group Services n.e.c.', 'Religious Services', 'Health and Fitness Centres and Gymnasia Operation'], dtype=object)
Limit the dataset to just food and drink services.
span>business_types = ['Cafes and Restaurants', 'Takeaway Food Services','Pubs, Taverns and Bars']
seats = pd.DataFrame(seats[seats['business_type'].isin(business_types)]).reset_index(drop=True)
seats.shape
(2042, 8)
# Selected seating types
seating_types = {'food':['Cafes and Restaurants', 'Takeaway Food Services'],
'drink': ['Pubs, Taverns and Bars']}
# Look at the types of seats
seats['seating_type'].unique()
array(['Seats - Indoor', 'Seats - Outdoor'], dtype=object)
# get an overview of the Trading Names
seats['trading_name'].unique()
array(['+39 Pizzeria & Antipasteria', '1000 Wat', '11 Inch Pizza', ...,
'Zouki on the Park', 'Zuppa', 'san churro docklands'], dtype=object)
food = pd.DataFrame(seats[seats['business_type'].isin(seating_types['food'])]).reset_index(drop=True)
food.shape
(1918, 8)
drink = pd.DataFrame(seats[seats['business_type'].isin(seating_types['drink'])]).reset_index(drop=True)
drink.shape
(124, 8)
indoor = pd.DataFrame(seats[seats['seating_type']=='Seats - Indoor']).reset_index(drop=True)
indoor.shape
(1794, 8)
outdoor = pd.DataFrame(seats[seats['seating_type']=='Seats - Outdoor']).reset_index(drop=True)
outdoor.shape
(248, 8)
This data contains locations of train stations and their accessibility information, such as hearing aid information.
# Get Train stations
url = f'{BASE_URL}{trains}/{url_filter}'
trains_result = requests.get(url)
trains_result_json = trains_result.json()
train_stations = pd.DataFrame(trains_result_json)
# View the first two rows of the dataset
train_stations.head(2)
| geo_point_2d | geo_shape | he_loop | lift | pids | station | |
|---|---|---|---|---|---|---|
| 0 | {'lon': 145.07955800000002, 'lat': -37.8688429... | {'type': 'Feature', 'geometry': {'coordinates'... | No | No | No | Alamein |
| 1 | {'lon': 144.82470999999998, 'lat': -37.7776559... | {'type': 'Feature', 'geometry': {'coordinates'... | No | No | Dot Matrix | Albion |
# Create 2 columns for lat and lon values
train_stations['lat'] = train_stations['geo_point_2d'].apply(lambda x: x['lat'])
train_stations['lon'] = train_stations['geo_point_2d'].apply(lambda x: x['lon'])
train_stations.head(2)
| geo_point_2d | geo_shape | he_loop | lift | pids | station | lat | lon | |
|---|---|---|---|---|---|---|---|---|
| 0 | {'lon': 145.07955800000002, 'lat': -37.8688429... | {'type': 'Feature', 'geometry': {'coordinates'... | No | No | No | Alamein | -37.868843 | 145.079558 |
| 1 | {'lon': 144.82470999999998, 'lat': -37.7776559... | {'type': 'Feature', 'geometry': {'coordinates'... | No | No | Dot Matrix | Albion | -37.777656 | 144.824710 |
# Create a function for controllling teh slider
def save_value(change):
global selected_value
selected_value = change['new']
# Create an IntSlider widget with range from 200 to 5000
slider = widgets.IntSlider(value = 5, min=1, max=10, step=1)
# Attach the save_value function to the slider's value attribute
slider.observe(save_value, names='value')
print('Select the radius in KMs from the CBD to limit Train stations:')
display(slider)
print('Select the radius in KMs from the CBD to limit Train stations:')
display(slider)
Select the radius in KMs from the CBD to limit Train stations:
# The below function will find the suburbs that fall within the sleected raduis from the CBD
def haversine(lat1, lon1, lat2, lon2):
R = 6371.0 # radius of the Earth in kilometers
lat1, lon1, lat2, lon2 = map(radians, [lat1, lon1, lat2, lon2])
dlat = lat2 - lat1
dlon = lon2 - lon1
a = sin(dlat/2)**2 + cos(lat1) * cos(lat2) * sin(dlon/2)**2
c = 2 * asin(sqrt(a))
distance = R * c
return distance
# Melbourne CBS lat / lon location
lat = -37.81368709240999 # latitude of the location
lon = 144.95738102347036 # longitude of the location
# set the slider selection to a variable
radius = slider.value
# Filter the DataFrame to select only rows that are within the radius
train_stations_CBD = train_stations[train_stations.apply(lambda row: haversine(row['lat'], row['lon'], lat, lon) <= radius, axis=1)].reset_index(drop=True)
# Print the filtered DataFrame
train_stations_CBD.head(3)
| geo_point_2d | geo_shape | he_loop | lift | pids | station | lat | lon | |
|---|---|---|---|---|---|---|---|---|
| 0 | {'lon': 145.0076200000001, 'lat': -37.827631} | {'type': 'Feature', 'geometry': {'coordinates'... | No | No | Dot Matrix | Burnley | -37.827631 | 145.007620 |
| 1 | {'lon': 144.963757, 'lat': -37.809699999999964} | {'type': 'Feature', 'geometry': {'coordinates'... | Yes | Yes | LCD | Melbourne Central | -37.809700 | 144.963757 |
| 2 | {'lon': 144.97340800000006, 'lat': -37.8119289... | {'type': 'Feature', 'geometry': {'coordinates'... | Yes | Yes | LCD | Parliament | -37.811929 | 144.973408 |
Get a list of Train station names (We will use this to create our button selectors)
station_list = train_stations_CBD['station'].to_list()
station_list
['Burnley', 'Melbourne Central', 'Parliament', 'West Richmond', 'Clifton Hill', 'Collingwood', 'Flagstaff', 'Flemington Bridge', 'Jewell', 'Jolimont', 'Prahran', 'Rushall', 'East Richmond', 'Flinders Street', 'Kensington', 'Macaulay', 'Newmarket', 'North Melbourne', 'North Richmond', 'Richmond', 'Royal Park', 'South Kensington', 'South Yarra', 'Southern Cross', 'Victoria Park']
The next three cells can be omitted if you are going to be selecting the friend group size and home stations yourself.
# Create an IntSlider widget with range from 200 to 5000
friend_slider = widgets.IntSlider(value = 7, min=1, max=12, step=1)
# Attach the save_value function to the slider's value attribute
friend_slider.observe(save_value, names='value')
print('Select the number of friends/ colleagues who are meeting in the city:')
display(friend_slider)
Select the number of friends/ colleagues who are meeting in the city:
'''This cell can be omitted if you are '''
# Select random selection of locations
friend_size = 7
#S tations
home_stations = random.sample(station_list, friend_size)
home_stations
['Jewell', 'Flagstaff', 'Newmarket', 'Flemington Bridge', 'Royal Park', 'Southern Cross', 'Parliament']
End of Optional cells
print(f'Radius Size = {slider.value} km')
Radius Size = 5 km
# Define the list to store selected station
selected_station = home_stations.copy()
# selected_station = [] #Empty list to use if you are not using the friend size
# Define the function to add or remove station
def add_remove_station(station):
global selected_station
if station in selected_station:
selected_station.remove(station)
else:
selected_station.append(station)
update_button_color()
# Define a function to update the button color based on the selected station list
def update_button_color():
for button in button_vbox.children:
if button.description in selected_station:
button.button_style = 'success'
else:
button.button_style = ''
# Create a VBox to display the buttons
button_vbox = widgets.VBox()
# Create the buttons and add them to the VBox
for station in station_list:
button = widgets.Button(description=station)
if station in selected_station:
button.button_style = 'success'
button.on_click(lambda event, station=station: add_remove_station(station))
button_vbox.children += (button,)
print("Select Stations:")
# Display the VBox
display(button_vbox)
Select Stations:
selected_station_df = train_stations_CBD[train_stations_CBD['station'].isin(selected_station)]
selected_station_df[['station', 'lat', 'lon']]
| station | lat | lon | |
|---|---|---|---|
| 2 | Parliament | -37.811929 | 144.973408 |
| 6 | Flagstaff | -37.811827 | 144.956447 |
| 7 | Flemington Bridge | -37.788137 | 144.939322 |
| 8 | Jewell | -37.774850 | 144.958749 |
| 16 | Newmarket | -37.786905 | 144.928886 |
| 20 | Royal Park | -37.781183 | 144.951868 |
| 23 | Southern Cross | -37.818354 | 144.952193 |
# Isolate the lat, lon values for the starting stations
locations = [tuple([x['lat'], x['lon']]) for x in selected_station_df['geo_point_2d']]
# Display the location (lat/lon pairs)
locations
[(-37.811928999999964, 144.97340800000006),
(-37.811826999999994, 144.95644700000003),
(-37.78813699999995, 144.93932200000006),
(-37.77484999999996, 144.958749),
(-37.78690499999999, 144.92888600000003),
(-37.781183, 144.951868),
(-37.818354, 144.95219300000008)]
def find_central_midpoint(coords):
'''Given a list of lat/lon pairs find the midpoint'''
# Convert coordinates to radians
latitudes, longitudes = zip(*coords)
latitudes = [math.radians(lat) for lat in latitudes]
longitudes = [math.radians(lon) for lon in longitudes]
# Calculate the midpoint
x = sum(math.cos(lat) * math.cos(lon) for lat, lon in zip(latitudes, longitudes))
y = sum(math.cos(lat) * math.sin(lon) for lat, lon in zip(latitudes, longitudes))
z = sum(math.sin(lat) for lat in latitudes)
n = len(coords)
lon_mid = math.atan2(y, x)
lat_mid = math.atan2(z, math.sqrt(x**2 + y**2))
return (math.degrees(lat_mid), math.degrees(lon_mid))
# find the geographical centre point between all stations
meeting_point = find_central_midpoint(locations)
# View the Geographocal Centre Points
meeting_point
(-37.79617002057846, 144.95155215362925)
First we need to create a base-layer map that we can add all of our dataset to.
#Create the base layer map
m = folium.Map(
# Lat, Lon for Melbourne CBD
location=[-37.81368709240999, 144.95738102347036],
tiles="cartodbpositron",
zoom_start=13,
control_scale=True,
prefer_canvas=True,
width=800,
height=580
)
# Display the map
m
A cluster is a collection of objects that we can add to a folium map as a single layer than can have thier visability turned on and off as a group.
# Create 4 food business clusters, 1 coworking space cluster and one for the central meeting zone
food_layer = MarkerCluster(name="Food").add_to(m)
drink_layer = MarkerCluster(name="Drink").add_to(m)
indoor_layer = MarkerCluster(name="Indoor").add_to(m)
outdoor_layer = MarkerCluster(name="Outdoor").add_to(m)
cowork_layer = MarkerCluster(name= "Coworking Spaces").add_to(m)
meeting_layer = MarkerCluster(name= "Central Meeting Location").add_to(m)
Identify some icons for each food/drink business group.
food_icon = 'glyphicon-cutlery'
drink_icon = 'glyphicon-glass'
indoor_icon = 'glyphicon-home'
outdoor_icon = 'glyphicon-tree-deciduous'
cowork_icon = 'glyphicon-lock'
Take a quick look at the two main datasets agian, Business seat counts, and Coworking Businesses.
# Food/ Drink business seats count Dataset
seats.head(2)
| seating_type | trading_name | business_type | number_of_seats | building_address | business_address | longitude | latitude | |
|---|---|---|---|---|---|---|---|---|
| 0 | Seats - Indoor | +39 Pizzeria & Antipasteria | Cafes and Restaurants | 66 | 362-364 Little Bourke Street MELBOURNE VIC 3000 | Ground 362 Little Bourke Street MELBOURNE VIC ... | 144.96175 | -37.81327 |
| 1 | Seats - Indoor | 1000 Wat | Cafes and Restaurants | 59 | 269-321 Lonsdale Street MELBOURNE VIC 3000 | Shop LG11 287 Lonsdale Street MELBOURNE VIC 3000 | 144.96393 | -37.81247 |
# Co-Working Businesses Dataset
cowork_details = coworking[['organisation', 'address', 'website', 'geopoint']].values.tolist()
cowork_details[1]
['360 Collins',
'360 Collins Street, Melbourne 3000',
'https://officespace.com.au/melbourne/153369/',
{'lon': 144.962311, 'lat': -37.816002}]
# Isolate the columns from the "seats" Dataset so that it can be used for all food/drink map clusters
columns = seats.columns.to_list()
# Add Food Layer
for row in food.itertuples():
location = row[columns.index('latitude')+1], row[columns.index('longitude')+1]
icon=folium.Icon(color='red', icon=food_icon, prefix='glyphicon')
html = f"<h3>{row[columns.index('trading_name')+1]}</h3><br>Address: {row[columns.index('business_address')+1]} <br>Total Seats: {row[columns.index('number_of_seats')+1]}"
iframe = folium.IFrame(html, width=300, height=130)
popup = folium.Popup(iframe, max_width=300)
marker = folium.Marker(location=location, popup=popup, icon=icon)
food_layer.add_child(marker)
# Add Drink Layer
for row in drink.itertuples():
location = row[columns.index('latitude')+1], row[columns.index('longitude')+1]
icon=folium.Icon(color='blue', icon=drink_icon, prefix='glyphicon')
html = f"{row[columns.index('trading_name')+1]}<br>Address: {row[columns.index('business_address')+1]} <br>Total Seats: {row[columns.index('number_of_seats')+1]}"
iframe = folium.IFrame(html, width=300, height=130)
popup = folium.Popup(iframe, max_width=300)
marker = folium.Marker(location=location, popup=popup, icon=icon, color='blue')
drink_layer.add_child(marker)
# Add Indoor Layer
for row in indoor.itertuples():
location = row[columns.index('latitude')+1], row[columns.index('longitude')+1]
icon=folium.Icon(color='purple', icon=indoor_icon, prefix='glyphicon')
html = f"{row[columns.index('trading_name')+1]}<br>Address: {row[columns.index('business_address')+1]} <br>Total Seats: {row[columns.index('number_of_seats')+1]}"
iframe = folium.IFrame(html, width=300, height=130)
popup = folium.Popup(iframe, max_width=300)
marker = folium.Marker(location=location, popup=popup, icon=icon)
indoor_layer.add_child(marker)
# Add Outdoor Layer
for row in outdoor.itertuples():
location = row[columns.index('latitude')+1], row[columns.index('longitude')+1]
icon=folium.Icon(color='green', icon=outdoor_icon, prefix='glyphicon')
html = f"{row[columns.index('trading_name')+1]}<br>Address: {row[columns.index('business_address')+1]} <br>Total Seats: {row[columns.index('number_of_seats')+1]}"
iframe = folium.IFrame(html, width=300, height=130)
popup = folium.Popup(iframe, max_width=300)
marker = folium.Marker(location=location, popup=popup, icon=icon)
outdoor_layer.add_child(marker)
# Add Co Working Layer
for row in cowork_details:
location = row[3]['lat'], row[3]['lon']
icon=folium.Icon(color='lightgray', icon=cowork_icon, prefix='glyphicon')
html = f'{row[0]}<br>Address: {row[1]} <br>Website: <a href="{row[2]}" target="blank">{row[2]}</a>'
iframe = folium.IFrame(html, width=300, height=130)
popup = folium.Popup(iframe, max_width=300)
marker = folium.Marker(location=location, popup=popup, icon=icon)
cowork_layer.add_child(marker)
# Add central location Layer
meeting_place = folium.Circle(
location=meeting_point,
radius=1000, # meters
color='red',
fill=True,
fill_opacity=0.2
)
meeting_layer.add_child(meeting_place)
folium.LayerControl().add_to(m)
<folium.map.LayerControl at 0x27036745940>
m
The above map provides an interactive resource for not only finidng the locatioon that is central to all parties, but also to find (and book using the business url) a coworking office space, and to also locate businesses nearby to that location which might be suitable to breakfast, lunch or after work drinks and nibbles.
This investigation can be expanded further to utilise actual addresses or tram/ bus stops (rather than train stations) as a means to find a central location between all parties that will be working together.